import pandas as pd

# 读取CSV文件，并直接将THEYEAR和SEASON列设为整数类型
product_prepare = pd.read_csv('product_prepare.csv')
material = pd.read_csv('MATERIAL.csv', dtype={'THEYEAR': int, 'SEASON': int})

# 合并数据
merged_data = pd.merge(product_prepare, material, left_on='product_id', right_on='CODE', how='left')

# 选择需要的列
final_data = merged_data[['product_id',
                           'product_class1',
                           'product_class2',
                           'product_saletag',
                           'product_color',
                           'product_name',
                           'BRANDCODE',
                           'THEYEAR',
                           'SEASON',
                           'product_price']]

# 重命名列
final_data.columns = ['product_id',
                      'product_class1',
                      'product_class2',
                      'product_saletag',
                      'product_color',
                      'product_name',
                      'product_brand',
                      'product_year',
                      'product_season',
                      'product_price']

# 过滤掉为'0'、空字符串和NaN的行，直接修改原DataFrame
final_data = final_data[
    (final_data['product_season'] != '0') &
    (final_data['product_season'] != '') &
    (final_data['product_season'].notna())
]
final_data = final_data[
    (final_data['product_year'] != '0') &
    (final_data['product_year'] != '') &
    (final_data['product_year'].notna())
]


print(final_data)



# 检查product_id是否唯一
unique_product_ids = final_data['product_id'].is_unique
if unique_product_ids:
    print("product_id是唯一的。")
else:
    print("product_id并不唯一。")

# 保存合并后的数据到CSV文件
final_data.to_csv('products.csv', index=False)
